package com.elinkway.infinitemovies.dao;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DBHelper extends SQLiteOpenHelper {

    private static final String DB_NAME = "infinitemovie.db";

    private static final int VERSION = 18;

    private final String CREATE_DOWNLOADPLAY_TABLE = "CREATE TABLE IF NOT EXISTS download_playrecord (id integer primary key autoincrement,aid text not null, name text, url text, time long,episode text,vt text, category_name text,seekHistory long,porder text, site text,request_type text)";

    private final String ADD_COLUMN_TO_FAVORITE = "ALTER TABLE favorite ADD COLUMN ";

    private final String CREATE_CHANNEL_CONTENT = "CREATE TABLE IF NOT EXISTS channel_content (id integer primary key autoincrement, page text unique,json text)";

    public DBHelper(Context context) {
        super(context, DB_NAME, null, VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        //db.execSQL("CREATE TABLE IF NOT EXISTS favorite (id integer primary key autoincrement,aid text unique, name text, desc text, vt text, src text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS favorite (id integer primary key autoincrement,aid text unique, name text, desc text, vt text, src text, nowepisode text, isend text,latestepisode text, favorite_time long, poster text)");
        db.execSQL("CREATE TABLE IF NOT EXISTS play_record (id integer primary key autoincrement,aid text unique, name text, url text, time long,episode text,vt text, category_name text,seekHistory long,porder text, site text,request_type text, vid text, is_upload text, poster text, page_index int, current_page_position int)");
        db.execSQL("CREATE TABLE IF NOT EXISTS search_record (id integer primary key autoincrement, name text unique)");
        db.execSQL(CREATE_DOWNLOADPLAY_TABLE);
        db.execSQL(CREATE_CHANNEL_CONTENT);
    }

    /**
     * 版本修改记录 版本号 修改内容 11 在10的基础上添加了一个播放记录的表
     * author qinguoli 版本修改记录 版本号 修改内容 12 在11的基础上为表 favorite 添加了nowepisode,isend两个字段
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        switch (oldVersion) {
            case 2:
                the2To10Upgrade(db);
            case 10:
                theElevenThModify(db);
            case 11:
                addColumnToFavorite(db);
            case 12:
                addLatestepisodeToFavorite(db);
            case 13:
                addIsUploadToRecord(db);
            case 14:
                alterDownPlayrecordAidAttri(db);
                addFavoriteTimeToFavorite(db);
            case 15:
                addPosterToFavorite(db);
                addPosterToRecord(db);
            case 16:    //避免最新版本的数据库又重新删除重建
                addPageIndexToRecord(db);
                addCurrentPageIndexToRecord(db);
            case 17:
                addChannelContentTable(db);
            case 18:
                break;
            default:
                db.execSQL("DROP TABLE IF EXISTS favorite;");
                db.execSQL("DROP TABLE IF EXISTS play_record;");
                db.execSQL("DROP TABLE IF EXISTS search_record;");
                db.execSQL("DROP TABLE IF EXISTS download_playrecord;");
                onCreate(db);
                break;
        }
    }

    /**
     * 增加图片
     *
     * @param db
     */
    private void addPosterToRecord(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE play_record ADD COLUMN poster text");
    }


    /**
     * 增加剧集页码
     *
     * @param db
     */
    private void addPageIndexToRecord(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE play_record ADD COLUMN page_index int");
    }

    /**
     * 增加当前剧集位置
     *
     * @param db
     */
    private void addCurrentPageIndexToRecord(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE play_record ADD COLUMN current_page_position int");
    }

    /**
     * 本地收藏增加图片字段
     *
     * @param db
     */
    private void addPosterToFavorite(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE favorite ADD COLUMN poster text");
    }

    /**
     * 本地收藏时增加收藏时间字段
     *
     * @param db
     */
    private void addFavoriteTimeToFavorite(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE favorite ADD COLUMN favorite_time long");
    }

    /**
     * 14-15在增加，改变本地播放记录的aid的属性，不为惟一值。
     *
     * @param db
     */
    private void alterDownPlayrecordAidAttri(SQLiteDatabase db) {
        db.execSQL("ALTER TABLE download_playrecord RENAME TO download_playrecord_old");
        db.execSQL(CREATE_DOWNLOADPLAY_TABLE);
        db.execSQL("INSERT INTO download_playrecord SELECT * FROM download_playrecord_old");
        db.execSQL("DROP TABLE download_playrecord_old");
    }

    /**
     * 13-14时，增加该播放记录是否上传服务器
     *
     * @param db
     */
    private void addIsUploadToRecord(SQLiteDatabase db) {
        //增加是否上传字段，0---没有上传；1---已经上传
        db.execSQL("ALTER TABLE play_record ADD COLUMN vid text");
        db.execSQL("ALTER TABLE play_record ADD COLUMN is_upload text");
    }

    /**
     * version 10 ->version 11 升级 <br>
     * 第十一次修改主要添加了favorite、play_record两个表 aid 不能为空的权限 添加下载管理的播放历史列表
     *
     * @param db zhangshuo 2014年6月12日 下午1:28:03
     */
    private void theElevenThModify(SQLiteDatabase db) {
        db.execSQL(CREATE_DOWNLOADPLAY_TABLE);
    }

    /**
     * version 2 ->version 10 升级 <br>
     * favorite表删除starring字段，增加desc字段<br>
     * play_record表增加category_name分类名, seekHistory播放进度, porder分集名, site来源站, request_type请求数据接口名字段
     *
     * @param db
     */
    private void the2To10Upgrade(SQLiteDatabase db) {
        // 旧表更名
        db.execSQL("ALTER TABLE favorite RENAME TO favoriteOld;");
        // 建立新表
        db.execSQL("CREATE TABLE favorite (id integer primary key autoincrement,aid text unique, name text, desc text, vt text, src text)");
        // 移植数据
        db.execSQL("INSERT INTO favorite (aid,name,vt,src) SELECT aid,name,vt,src FROM favoriteOld");
        // 删除旧表
        db.execSQL("DROP TABLE favoriteOld;");
        // play_record增加新字段
        db.execSQL("ALTER TABLE play_record ADD COLUMN category_name text");
        db.execSQL("ALTER TABLE play_record ADD COLUMN seekHistory long");
        db.execSQL("ALTER TABLE play_record ADD COLUMN porder text");
        db.execSQL("ALTER TABLE play_record ADD COLUMN site text");
        db.execSQL("ALTER TABLE play_record ADD COLUMN request_type text");
    }

    /**
     * version 11 -> 12
     * 第12次给表favorite添加了nowepisode,isend
     * qinguoli 2014年6月25日
     **/
    public void addColumnToFavorite(SQLiteDatabase db) {
        //表尾增加列vt
        db.execSQL("ALTER TABLE favorite ADD COLUMN nowepisode text");
        //表尾增加列isend
        db.execSQL("ALTER TABLE favorite ADD COLUMN isend text");
    }

    /**
     * version 12 -> 13
     * 第13次给表favorite添加了latestepisode
     * daipei 2014年7月12日
     **/
    public void addLatestepisodeToFavorite(SQLiteDatabase db) {
        //表尾增加列latestepisode
        db.execSQL("ALTER TABLE favorite ADD COLUMN latestepisode text");
    }

    /**
     * version 17->18
     * 增加频道内容缓存表
     * @param db
     */
    public void addChannelContentTable(SQLiteDatabase db) {
        db.execSQL(CREATE_CHANNEL_CONTENT);
    }

}
